package com.cxm.report.dao;

import com.cxm.report.dao.provider.ChannelBrandReportProvider;
import com.cxm.report.model.criteria.ReportFilter;
import com.cxm.report.model.data.ChannelBrandReport;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author scl.BlueOcean
 * @version 1.0
 * @date 2018年07月03 15:04
 * @description
 */
@Mapper
public interface ChannelBrandReportDao {
    /**删除指定日期的报表数据*/
    @DeleteProvider(type = ChannelBrandReportProvider.class,method = "delete")
    void delete(String date);
    /**查询订单中的品牌id,创建时间*/
    @InsertProvider(type = ChannelBrandReportProvider.class,method = "getBrandInfo")
    int insertBrandInfo(String date);
    /**更新brand_name字段*/
    @UpdateProvider(type = ChannelBrandReportProvider.class,method = "updateBrandName")
    void updateBrandName(String date);
    /**更新channelName字段*/
    @UpdateProvider(type = ChannelBrandReportProvider.class,method = "updateChannelName")
    void updateChannelName(String date);
    /**更新查询成功数和平均查询时长*/
    @UpdateProvider(type = ChannelBrandReportProvider.class,method = "updateSuccessNum")
    void updateSuccessNum(String date);
    /**更新查询失败数*/
    @UpdateProvider(type = ChannelBrandReportProvider.class,method = "updateFailedNum")
    void updateFailedNum(String date);
    /**更新查询记录数*/
    @UpdateProvider(type = ChannelBrandReportProvider.class,method = "updateNoRecordNum")
    void updateNoRecordNum(String date);
    /**更新查询中数*/
    @UpdateProvider(type = ChannelBrandReportProvider.class,method = "updateInquiryNum")
    void updateInquiryNum(String date);
    /**更新查询超时数*/
    @UpdateProvider(type = ChannelBrandReportProvider.class,method = "updateOverTimeNum")
    void updateOverTimeNum(String date);

    @Select(value = {"select brandName, orderNum, successNum, failedNum, noRecordNum, overTimeNum, inquiryNum, avgQueryTime from t_day_report where report_date=#{date} order by orderNum desc"})
    List<ChannelBrandReport> selectReport(String date);

    @Select(value = {"select brandName,channelName, orderNum, successNum, failedNum, noRecordNum, overTimeNum, inquiryNum, report_date from t_channel_brand_report where channelName IS NOT NULL and report_date >= #{begin} and report_date <= #{end} order by channelName,report_date desc, orderNum desc"})
    List<ChannelBrandReport> selectReports(ReportFilter filter);
    @Select(value = "select brandName, channelName,sum(orderNum)orderNum, sum(successNum)successNum, \n" +
            "sum(failedNum)failedNum, sum(noRecordNum)noRecordNum, sum(overTimeNum)overTimeNum,\n" +
            "sum(inquiryNum)inquiryNum " +
            "from t_channel_brand_report " +
            "where channelName IS NOT NULL and report_date >= #{begin} and report_date <= #{end}" +
            "group by channelName,brandName " +
            "order by  report_date desc, channelName,orderNum desc")
    List<ChannelBrandReport> selectReportsOfScope(ReportFilter filter);

    /**
     * 按月统计
     * @param filter
     * @return
     */
    @Select(value = {"SELECT brandName, channelName,SUM(orderNum) orderNum, SUM(successNum)successNum, SUM(failedNum)failedNum," +
            "SUM( noRecordNum)noRecordNum, SUM(overTimeNum)overTimeNum, SUM(inquiryNum)inquiryNum, " +
            "AVG(avgQueryTime)avgQueryTime,DATE_FORMAT(report_date, '%Y-%m') reportDate " +
            "FROM t_channel_brand_report " +
            "WHERE channelName IS NOT NULL and report_date >= #{begin} AND report_date <= #{end} " +
            "GROUP BY channelName,brandName,reportDate " +
            "order by reportDate DESC,channelName, orderNum DESC"})
    List<ChannelBrandReport> selectReportsGroupMonth(ReportFilter filter);
    /**
     * 按周统计
     * @param filter
     * @return
     */
    @Select(value = {"SELECT brandName, channelName,SUM(orderNum) orderNum, SUM(successNum)successNum, SUM(failedNum)failedNum," +
            "SUM( noRecordNum)noRecordNum, SUM(overTimeNum)overTimeNum, SUM(inquiryNum)inquiryNum, " +
            "CONCAT(date_sub(report_date,INTERVAL WEEKDAY(report_date) DAY) ,' To ', date_sub(report_date,INTERVAL WEEKDAY(report_date) - 6 DAY) )reportDate " +
            "FROM t_channel_brand_report " +
            "WHERE channelName IS NOT NULL and report_date >= #{begin} AND report_date <= #{end} " +
            "GROUP BY channelName,brandName,reportDate " +
            "order by reportDate DESC,channelName, orderNum DESC"})
    List<ChannelBrandReport> selectReportsGroupWeek(ReportFilter filter);



    int count(String date);
}
